package com.oscarmovie;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLConnection;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import tt.connection.database.DatabaseConnection;
import tt.connection.database.OracleConnection;
import tt.util.MD5;
import tt.util.TimeUtils;

public class Tools {
	private static final String IMAGE_PATH = "e:/eclipse/workspace/OscarMovie/uploaded/movie/";

	public static String getOnlyYear(Date d) {
		return TimeUtils.getOnlyYear(d);
	}

	public static void main(String args[]) {
		
		
		//seperateImages();
	}
	public static String parse(String ss,String start, String end) {
		int p1 = ss.indexOf(start)+start.length();
		int p2 = ss.indexOf(end, p1);
		String title = ss.substring(p1, p2);
		return title;
	}
	private static void seperateImages(){
		
		List<HashMap<Object, Object>> select = getDc().select(
				//"select data from image where exists (select * from movie_image where image_id = image.id)");
				"select data from image where exists (select * from movie where image_id = image.id)");
		System.out.println(select.size());
		for (HashMap<Object, Object> hm : select) {
			String name = hm.get("DATA") == null ? null : hm.get("DATA").toString();
			String srcFile = "d:\\eclipse\\workspace\\OscarMovie\\uploaded\\images\\"+name;
			String destFile = "d:\\eclipse\\workspace\\OscarMovie\\uploaded\\images\\movie\\thumb\\"+name;
			File src = new File(srcFile);
			File dest = new File(destFile);
			src.renameTo(dest);
			System.out.println(dest);
		}
	}
	private static void refineMovieNameCn() {
		List<HashMap<Object, Object>> retrieveMovies = retrieveMovies();
		System.out.println(retrieveMovies.size());
		for (HashMap<Object, Object> hm : retrieveMovies) {
			int movieId = ((BigDecimal) hm.get("ID")).intValue();
			String name = hm.get("NAME") == null ? null : hm.get("NAME").toString();
			String alias = hm.get("ALIAS") == null ? null : hm.get("ALIAS").toString();

			if (!name.equals(alias)) {
				String newName = alias.replace(name, "");
				System.out.println(newName.trim());
				updateMovieNameCn(movieId, newName.trim());
			}
			System.out.println("===============");
		}
	}

	private static void refinePersonNameCn() {
		List<HashMap<Object, Object>> retrieveMovies = retrievePeople();
		System.out.println(retrieveMovies.size());
		for (HashMap<Object, Object> hm : retrieveMovies) {
			int movieId = ((BigDecimal) hm.get("ID")).intValue();
			String name = hm.get("NAME") == null ? null : hm.get("NAME").toString();
			String alias = hm.get("ALIAS") == null ? null : hm.get("ALIAS").toString();

			if (!name.equals(alias)) {
				String newName = alias.replace(name, "");
				System.out.println(newName.trim());
				updatePersonNameCn(movieId, newName.trim());
			}
			System.out.println("===============");
		}
	}

	// /////////////////////////////////////////////////////////////////////////////////////////////
	public static void updateMovie(int movieId, String movieName, String length, String date, String plot,
			String imageUrl, String languages[], String categories[], String countries[]) {

		if (movieName == null)
			movieName = "";
		if (length == null)
			length = "0";
		if (plot == null)
			plot = "";
		if (date == null)
			date = "2020";
		if (date.length() == 4)
			date += "-01-01";
		if (languages == null)
			languages = new String[0];
		if (categories == null)
			categories = new String[0];
		if (countries == null)
			countries = new String[0];

		for (String language : languages) {
			language = language.trim();
			int id = retrieveLanguageId(language);
			createMovieLanguage(movieId, id);
		}

		for (String category : categories) {
			category = category.trim();
			int id = retrieveCategoryId(category);
			createMovieCategory(movieId, id);
		}

		for (String country : countries) {
			country = country.trim();
			int id = retrieveCountryId(country);
			createMovieCountry(movieId, id);
		}

		// int imageId = createImage(movieId, imageUrl);

		updateMovieSelf(movieId, movieName, length, date, plot);
	}

	private static void updateMovieSelf(int movieId, String movieName, String length, String date, String plot) {
		String[] values = { movieName, String.valueOf(length), String.valueOf(plot), String.valueOf(movieId) };
		String sql = "update movie set alias=?, length=?, datetime=to_date('" + date + "','yyyy-mm-dd'), plot=?,  douban_id=-douban_id where id=?";
		sql = "update movie set alias=?, length=?,  plot=?,  douban_id=-douban_id where id=?";
		printSql(values, sql);
		getDc().update(sql, values);
	}

	public static void updateMovieNameCn(int movieId, String nameCn) {
		String[] values = { nameCn, String.valueOf(movieId) };
		String sql = "update movie set name_cn=? where id=?";
		printSql(values, sql);
		getDc().update(sql, values);
	}

	public static void updatePersonNameCn(int personId, String nameCn) {
		String[] values = { nameCn, String.valueOf(personId) };
		String sql = "update person set name_cn=? where id=?";
		printSql(values, sql);
		getDc().update(sql, values);
	}

	public static void updateImageId(String table, int id, int imageId) {
		String[] values = { String.valueOf(imageId), String.valueOf(id) };
		String sql = "update "+table+" set image_id=?, mtime_id=-mtime_id where id=?";
		printSql(values, sql);
		getDc().update(sql, values);
	}
	
	public static void updateMtimeLength(int length, int id) {
		String[] values = { String.valueOf(length), String.valueOf(id) };
		String sql = "update movie set mtimelength=? where id=?";
		printSql(values, sql);
		getDc().update(sql, values);
	}
	public static void updateMtimeDate(String date, int id) {
		String[] values = {  String.valueOf(id) };
		String sql = "update movie set mtimedatetime=to_date('" + date + "','yyyy-mm-dd') where id=?";
		printSql(values, sql);
		getDc().update(sql, values);
	}
	
	public static List<HashMap<Object, Object>> retrieveObjectWithDoubanIds(String table) {
		List<HashMap<Object, Object>> select = getDc().select("select id, douban_id from "+table+" where douban_id > 0");
		return select;
	}
	public static List<HashMap<Object, Object>> retrieveObjectWithoutDoubanId(String table) {
		List<HashMap<Object, Object>> select = getDc().select(
				"select id, name, name_cn from "+table+" where douban_id = 0 or douban_id is null");
		return select;
	}
	public static List<HashMap<Object, Object>> retrieveMovies() {
		List<HashMap<Object, Object>> select = getDc().select(
				"select id,name, name_cn, alias from movie where name_cn is null and alias is not null");
		return select;
	}

	public static List<HashMap<Object, Object>> retrievePeople() {
		List<HashMap<Object, Object>> select = getDc().select(
				"select id,name, name_cn, alias from person where name_cn is null and alias is not null");
		return select;
	}

	public static List<HashMap<Object, Object>> retrieveMtimeObjects(String table) {
		List<HashMap<Object, Object>> select = getDc().select(
				"select id, name, name_cn from " + table + " where mtimeid = -2");
		return select;
	}

	
	public static List<HashMap<Object, Object>> retrieveObjectsWithMtimeIds(String table) {
		List<HashMap<Object, Object>> select = getDc().select("select id, mtime_id from "+table+" where mtime_id >0 ");
		return select;
	}
	public static List<HashMap<Object, Object>> retrieveObjectsWithoutName(String table) {
		List<HashMap<Object, Object>> select = getDc().select("select id,mtime_id from "+table+" where mtime_id <0 and  name_cn is null");
		return select;
	}
	public static void updateMovieDoubanId(int movieId, int doubanId) {
		String[] values = { "" + doubanId, "" + movieId };
		String sql="update movie set douban_id=? where id=?";
		printSql(values,sql);
		getDc().update(sql, values);
	}
	public static void updateDoubanIdFlag(int movieId) {
		String[] values = { "" + movieId };
		String sql="update movie set douban_id=-douban_id where id=?";
		printSql(values,sql);
		getDc().update(sql, values);
	}
	public static void updateMovieMtimeId(int movieId, int mtimeId) {
		String[] values = { "" + mtimeId, "" + movieId };
		getDc().update("update movie set mtimeid=? where id=?", values);
	}

	public static void updatePersonMtimeId(int personId, int mtimeId) {
		String[] values = { "" + mtimeId, "" + personId };
		getDc().update("update person set mtime_id=? where id=?", values);
	}

	
	public static void updatePersonDoubanId(int personId, int doubanId) {

		String[] values = { "" + doubanId, "" + personId };
		getDc().update("update person set douban_id=? where id=?", values);
	}

	public static void updatePerson(int personId, String alias) {

		String[] values = { alias, String.valueOf(personId) };
		String sql = "update person set alias=?, douban_id=-douban_id where id=?";
		printSql(values, sql);
		getDc().update(sql, values);
	}
	public static void updateAlias(String table, int personId, String alias) {

		String[] values = { alias, String.valueOf(personId) };
		String sql = "update "+table+" set alias=? where id=?";
		printSql(values, sql);
		getDc().update(sql, values);
	}
	public static List<HashMap<Object, Object>> retrieveImages() {
		List<HashMap<Object, Object>> select = getDc().select("select id,data,url from image");
		return select;
	}

	static DatabaseConnection dc = null;

	private static DatabaseConnection getDc() {
		String url = "jdbc:oracle:thin:@SHIMOSORCL:1521:George";
		//url = "jdbc:oracle:thin:@localhost:1521";
		String user = "tt";
		String password = "tt";
		if (dc == null)
			dc = new OracleConnection(url, user, password);

		return dc;
	}
	public static void updateImage(int imageId, String type, int parentId) {
		String sql = null;
		String[] values = { type, String.valueOf(parentId), String.valueOf(imageId) };
		
		if("M".equals(type)){
			sql = "update image set type=?, movie_id=? where id=?";
		} else if("P".equals(type)){
			 sql = "update image set type=?, person_id=? where id=?";
		}
		
		printSql(values, sql);
		getDc().update(sql, values);
	}
	
	/**
	 * @deprecated
	 * */
	public static void createRelationShip(String table, int leftId, int rightId) {
		String values[] = { String.valueOf(leftId), String.valueOf(rightId) };
		String[] split = table.split("_");
		String tableLeft= split[0];
		String tableRight= split[1];
		String sql = "select count(*) c from " + table + " where "+tableLeft+"_id = ? and " + tableRight
				+ "_id = ?";
		printSql(values, sql);

		List<HashMap<Object, Object>> select = getDc().select(sql, values);

		HashMap<Object, Object> hashMap = select.get(0);
		BigDecimal bd = (BigDecimal) hashMap.get("C");
		if (bd.intValue() == 0) {
			sql = "insert into " + table + " values (?,?)";
			printSql(values, sql);
			DatabaseConnection dc = getDc();
			dc.insert(sql, values);
		}

	}

	public static void redownload() {
		List<HashMap<Object, Object>> retrieveImages = retrieveImages();
		int size = retrieveImages.size();
		int i = 0;
		for (HashMap<Object, Object> hm : retrieveImages) {
			String data = hm.get("DATA") == null ? "" : hm.get("DATA").toString();
			String url = hm.get("URL") == null ? null : hm.get("URL").toString();
			if (url != null) {
				System.out.println("No. " + (++i) + "/" + size + ": " + url + ", " + data);
				download(url, IMAGE_PATH + data, 1);
			}
		}
	}
	public static int createImage(String imageUrl) {
		return createImage(imageUrl,null,null,null);
	}
	public static int createImage(String imageUrl, String nameCn, String daxiao, String chicun) {
		int imageId = 0;
		if (imageUrl == null || imageUrl.equals(""))
			return imageId;
		String suffixName = imageUrl.substring(imageUrl.lastIndexOf("."));
		String imageName = MD5.getMD5LowerCase(imageUrl) + suffixName;
		System.out.println("Image url/name: " + imageUrl + ": " + imageName);
		
		//CHECK if the image is already in DB, if YES, retrieve its ID.
		String sql = "select id from image where data=?";
		String[] vv = { imageName };
		List<HashMap<Object, Object>> dd = getDc().select(sql,vv);
		if (dd.size()>0) {
			HashMap<Object, Object> hashMap = dd.get(0);
			BigDecimal s = (BigDecimal) hashMap.get("id");
			imageId = s.intValue();
		}
		
		if (imageId == 0 && download(imageUrl, IMAGE_PATH + imageName, 1)) {
			// id
			sql = "select sq_image_id.nextval id from dual";
			System.out.println(sql);
			List<HashMap<Object, Object>> select = getDc().select(sql);

			HashMap<Object, Object> hashMap = select.get(0);
			BigDecimal s = (BigDecimal) hashMap.get("id");
			imageId = s.intValue();

			// image
			String[] values = { String.valueOf(imageId), imageName, imageUrl,nameCn,  daxiao,  chicun };
			sql = "insert into image (id, data, url, name_cn, length, property) values (?, ?, ?,?,?,?)";
			printSql(values, sql);
			getDc().insert(sql, values);

		}
		return imageId;
	}

	private static void printSql(String[] values, String sql) {

		System.out.print("[" + sql + "] ");
		for (String s : values) {
			System.out.print("[" + s + "] ");
		}
		System.out.println();
	}

	private static void createMovieLanguage(int movieId, int languageId) {
		createRelationShip("movie_language", movieId, languageId);
	}

	private static void createMovieCategory(int movieId, int languageId) {
		createRelationShip("movie_category", movieId, languageId);
	}

	private static void createMovieCountry(int movieId, int languageId) {
		createRelationShip("movie_country", movieId, languageId);
	}

	private static int retrieveId(String tableName, String name) {
		int id = 0;
		String v[] = { name };
		String sql = "select id, name from " + tableName + " where name_cn = ?";
		printSql(v, sql);

		List<HashMap<Object, Object>> select = getDc().select(sql, v);
		if (select.size() == 0) {
			sql = "select sq_" + tableName + "_id.nextval id from dual";
			System.out.println(sql);
			List<HashMap<Object, Object>> select2 = getDc().select(sql);

			HashMap<Object, Object> hashMap = select2.get(0);

			BigDecimal s = (BigDecimal) hashMap.get("id");
			id = s.intValue();

			String[] values = { String.valueOf(id), name, name };
			sql = "insert into " + tableName + " values (?, ?,?)";
			printSql(values, sql);

			getDc().insert(sql, values);

		} else {

			HashMap<Object, Object> hashMap = select.get(0);
			BigDecimal s = (BigDecimal) hashMap.get("ID");
			id = s.intValue();
		}
		return id;
	}

	private static int retrieveCategoryId(String name) {
		return retrieveId("category", name);
	}

	private static int retrieveLanguageId(String name) {
		return retrieveId("language", name);
	}

	private static int retrieveCountryId(String name) {
		return retrieveId("country", name);
	}

	
	public static boolean download(String src, String dest, int tried) {
		boolean b = false;
		if (tried < 3) {// retry
			try {
				int bufferSize = 1024 * 8;
				byte[] buffer = new byte[bufferSize];
				InputStream is = getInputStream(src);
				OutputStream os = getOutputStream(dest);
				int geted = 0;
				while ((geted = is.read(buffer, 0, buffer.length)) != -1) {
					os.write(buffer, 0, geted);
					os.flush();
					buffer = new byte[bufferSize];
				}
				b = true;
				is.close();
				os.close();
			} catch (Exception e) {
				String errorMsg = e.getMessage();
				if (errorMsg.startsWith("Connection reset")) {
					download(src, dest, ++tried);
				}
			}
		}
		return b;
	}

	private static OutputStream getOutputStream(String dest) throws FileNotFoundException {
		File filename = new File(dest);
		if (!filename.getParentFile().exists()) {
			filename.getParentFile().mkdirs();
		}
		return new FileOutputStream(dest);
	}

	private static InputStream getInputStream(String src) throws IOException {
		URL url = new URL(src);
		HttpURLConnection openConnection = (HttpURLConnection) url.openConnection();
		openConnection.setReadTimeout(30000);
		return new URL(src).openStream();
	}

	

}
